IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[GetCustomReportList]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[GetCustomReportList]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
                  
/******************************************************************                
* Name:   [GetCustomReportList]                
* Purpose:  Retrieves details for the ids manipulated(after performing sustom report search)joining with required tables.                 
*                
* PARAMETERS(IN)                
* Name          Description                     
* -------------      -------------------------------------------                
* @xml       xml Document containing list of Ids(REQUIRED)    

The passed in XML has the following form:    
  <NewDataSet>    
   <Table>    
    <Id>234711</Id> 
	<SupervisorId>78543</SupervisorId>
	<COIId>525</COIId>  
    </Table>    
   <Table>    
    <Id>234712</Id> 
	<SupervisorId>79543</SupervisorId>
	<COIId>734</COIId>   
   </Table>    
   ...    
  </NewDataSet>               
*********************************************************************/                
                
CREATE PROCEDURE [dbo].[GetCustomReportList]                
(                
@xml text                
)                
AS BEGIN                
SET NOCOUNT ON                
                
 declare @xmlDoc int                  
 declare @admPersonIds table (Sequence int identity, [ID] int,[SupervisorId] int,CoiId int)                  
 exec sp_xml_preparedocument @xmlDoc output,@xml   
 insert into @admPersonIds select [Id],[SupervisorId],[COIid] from openxml(@xmlDoc, '//IdList', 2) with (Id int,SupervisorId int,COIid int)                  
 exec sp_xml_removedocument @xmlDoc                
                
SELECT adp.[Id],                
     adp.[lastname]+', '+adp.[firstname] as [Name],       
	 adp.Title,  
	 adp1.[lastname]+', '+adp1.[firstName] as [Supervisor],              
	 coi.[Id] as [COIId],              
     coi.[PolicyViolation],                
     coi.[HasOutsideActivities] as HasOutsideActivity ,                
     coi.[HasEquityInterests],                
     coi.[HasFamilyAssociations] as [HasFamilyInterests],                
     coi.[OtherActivity],                
     coi.[LastNotified],                
     coi.[DateSubmitted],                
     coi.[DateReviewed],      
     coi.[DocumentId],
     coi.[Royalty]             
   
  FROM AdmPerson adp           
  Inner JOIN @admPersonIds tempTable              
  on tempTable.[Id] =adp.[Id]            
  Inner JOIN ConflictOfInterest coi             
  on coi.[Id]= tempTable.[CoiId]            
  Left Outer Join AdmPerson adp1             
  on adp1.[Id]= tempTable.SupervisorId        
               
END 




